package es.ats.codington.festival.daos;



import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import es.ats.codington.festival.exceptions.DAOSExceptions;
import es.ats.codington.festival.interfaces.daos.iEventSignUpDAO;
import es.ats.codington.festival.utils.FERSDataConnection.FersDC;
import es.ats.codington.festival.utils.Query.JSonQuery;

public class EventSignUpDAOImpl implements iEventSignUpDAO {
	
	private Connection connection;
	private PreparedStatement statement;
	//JSonQuery json=new JSonQuery();

	
	/**
	 * Constructor 
	 * @param connection
	 * @param statement
	 * @param resultSet
	 */
	public EventSignUpDAOImpl(Connection connection) {
		super();
		this.connection = connection;
	
	}
	
	public EventSignUpDAOImpl() {
		super();
	}
	
	/**
	 * @param idvisistor
	 * @param idevent
	 * @return boolean
	 */
	@Override
	public boolean signUpToEvent(int iDvisitor, int iDevent)throws Exception {

		
		int affectedRows=0;
		
		try{			
			
			connection.setAutoCommit(false);
			statement = connection.prepareStatement("INSERT INTO `visitor-places` (`idvisitor`, `idevent`) VALUES (?,?)");
			statement.setInt(1, iDvisitor);
			statement.setInt(2, iDevent);
			
			statement.executeUpdate();

			//Modifying the seats available
			int sum= 0;
			PreparedStatement statement1;
			statement1 = connection.prepareStatement("SELECT `seats` FROM `event` where idevent=?");
			statement1.setInt(1, iDevent);
			
			ResultSet cosa1 =statement1.executeQuery();
			while(cosa1.next()){
			sum=cosa1.getInt("seats");
			}
			if (sum==0){
				throw new Exception("No more available seats");				
			}else{
				sum=sum -1;
			}
			PreparedStatement statement2;
			statement2 = connection.prepareStatement("UPDATE event SET seats=? WHERE idevent = ?");
			statement2.setInt(1,sum);
			statement2.setInt(2, iDevent);

			affectedRows=statement2.executeUpdate();
			connection.commit();

		}catch (Exception ex) {
			connection.rollback();
			throw ex;
			
		}
		
		if(affectedRows!=0){
			return true;
		}
		else{
		return false;
		}
		
	}


	/**
	 * @param idvisistor
	 * @param idevent
	 * @return
	 */
	@Override
	public boolean unsignUpToEvent(int iDvisitor, int iDevent)throws Exception {
			int affectedRows=0;
try{
			
			connection.setAutoCommit(false);
			
			statement = connection.prepareStatement("DELETE FROM `visitor-places`WHERE idvisitor=? and idevent=?");
			
			statement.setInt(1, iDvisitor);
			statement.setInt(2, iDevent);
			
			statement.executeUpdate();
			
			int sum= 0;
			PreparedStatement statement1;
			statement1 = connection.prepareStatement("SELECT `seats` FROM `event` where idevent=?");
			statement1.setInt(1, iDevent);
			
			ResultSet cosa2 =statement1.executeQuery();
			while(cosa2.next()){
			sum=cosa2.getInt("seats");
			}
			sum=sum +1;
			

			PreparedStatement statement2;
			statement2 = connection.prepareStatement("UPDATE event SET seats=? WHERE idevent = ?");
			statement2.setInt(1,sum);
			statement2.setInt(2, iDevent);

			affectedRows=statement2.executeUpdate();
			connection.commit();
			
		
			

		}catch (Exception ex) {
						
			connection.rollback();
			throw ex;
		}
		
		if(affectedRows!=0){
			return true;
		}
		else{
		return false;
		}
	}

	
	

} 

